package com.foreknow.elm.dao.impl;

import com.foreknow.elm.dao.DeliveryAddressDao;
import com.foreknow.elm.po.Deliveryaddress;
import com.foreknow.elm.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class DeliveryAddressDaoImpl implements DeliveryAddressDao {
    private Connection con = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;

    @Override
    public List<Deliveryaddress> listDeliveryAddressByUserId(String userId) throws Exception {
        String sql="select * from Deliveryaddress where userId=?";
        List<Deliveryaddress> list = new ArrayList<>();
        Deliveryaddress dls=new Deliveryaddress();
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1, userId);
            rs = pst.executeQuery();
            while(rs.next()){
                dls.setDaId(rs.getInt("daid"));
                dls.setContactName(rs.getString("contactName"));
                dls.setContactSex(rs.getInt("contactSex"));
                dls.setContactTel(rs.getString("contactTel"));
                dls.setAddress(rs.getString("address"));
                dls.setUserId(rs.getString("userId"));
                list.add(dls);
            }
        }
        finally {
            DBUtil.close(rs,pst);
        }
        return list;
    }

    @Override
    public Deliveryaddress getDeliveryAddressById(int daId) throws Exception {
        String sql="select * from Deliveryaddress where daId=?";
        Deliveryaddress dls=new Deliveryaddress();
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setInt(1, daId);
            rs = pst.executeQuery();
            while (rs.next()) {
                dls.setDaId(rs.getInt("daid"));
                dls.setContactName(rs.getString("contactName"));
                dls.setContactSex(rs.getInt("contactSex"));
                dls.setContactTel(rs.getString("contactTel"));
                dls.setAddress(rs.getString("address"));
                dls.setUserId(rs.getString("userId"));
            }
        }
        finally {
            DBUtil.close(rs,pst);
        }
        return dls;
    }

    @Override
    public Integer saveDeliveryAddress(Deliveryaddress dl) throws Exception {
        int result=0;
        String sql="insert into Deliveryaddress(contactName,contactSex,contactTel,address,userId) values(?,?,?,?,?)";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1, dl.getContactName());
            pst.setInt(2, dl.getContactSex());
            pst.setString(3, dl.getContactTel());
            pst.setString(4, dl.getAddress());
            pst.setString(5, dl.getUserId());
            result = pst.executeUpdate();
        }
        finally {
            DBUtil.close(rs,pst);
        }
        return result;
    }

    @Override
    public Integer updateDeliveryAddress(Deliveryaddress dl) throws Exception {
        int result = 0;
        String sql="update Deliveryaddress set contactName=?,contactSex=?,contactTel=?,address=?,userId=? where daId=?";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1, dl.getContactName());
            pst.setInt(2, dl.getContactSex());
            pst.setString(3, dl.getContactTel());
            pst.setString(4, dl.getAddress());
            pst.setString(5, dl.getUserId());
            pst.setInt(6, dl.getDaId());
            result = pst.executeUpdate();
        }
        finally {
            DBUtil.close(rs,pst);
        }
        return result;
    }

    @Override
    public Integer removeDeliveryAddress(int daId) throws Exception {
        int result =0;
        String sql="delete from DeliveryAddress where daId=?";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setInt(1, daId);
            result = pst.executeUpdate();
        }
        finally {
            DBUtil.close(rs,pst);
        }
        return result;
    }
}
